package com.maserb.monitor.repository.impl;

import com.maserb.monitor.common.IdUtils;
import com.maserb.monitor.database.JdbcTemplateFactory;
import com.maserb.monitor.database.PreparedStatementUtils;
import com.maserb.monitor.entity.WaveEntity;
import com.maserb.monitor.repository.WaveRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Calendar;

@Repository
public class WaveRepositoryImpl implements WaveRepository {
    @Autowired
    JdbcTemplateFactory jdbcTemplateFactory;

    @Override
    public void save(WaveEntity entity, String deviceCode) {
        JdbcTemplate jdbcTemplate = jdbcTemplateFactory.getTemplate(deviceCode);
        String sampleTableName = getSampleTablename(Calendar.getInstance().get(Calendar.YEAR));
        String dataTableName = getWavaDataTablename(Calendar.getInstance().get(Calendar.YEAR));
        makesureTableExists(jdbcTemplate, sampleTableName, dataTableName);
        entity.setId(IdUtils.generateId());

        saveSample(entity, jdbcTemplate, sampleTableName);
        saveData(entity, jdbcTemplate, dataTableName);
    }

    private void saveData(WaveEntity entity, JdbcTemplate jdbcTemplate, String tablename) {
        String insertDataSql = "insert into " + tablename + "(data,id) " +
                "values(?,?)";
        jdbcTemplate.update(insertDataSql, ps -> {
            try {
                PreparedStatementUtils.setFloats(ps, 1, entity.getData());
            } catch (Exception e) {
                e.printStackTrace();
            }
            PreparedStatementUtils.setString(ps, 2, entity.getId());
        });
    }

    private void saveSample(WaveEntity entity, JdbcTemplate jdbcTemplate, String tablename) {
        String insertSampleSql = "insert into " + tablename + "(deviceCode,pointCode,sampleTime,sampleFrequence,id) " +
                "values(?,?,?,?,?)";

        jdbcTemplate.update(insertSampleSql, ps -> {
            PreparedStatementUtils.setString(ps, 1, entity.getDeviceCode());
            PreparedStatementUtils.setString(ps, 2, entity.getPointCode());
            PreparedStatementUtils.setDate(ps, 3, entity.getSampleTime());
            PreparedStatementUtils.setFloat(ps, 4, entity.getSampleFrequence());
            PreparedStatementUtils.setString(ps, 5, entity.getId());
        });
    }

    private void makesureTableExists(JdbcTemplate jdbcTemplate, String sampleTableName, String dataTableName) {
        String existTableSql = "SELECT COUNT(*) FROM information_schema.\n" +
                "TABLES WHERE TABLE_NAME='" + sampleTableName + "'";

        Integer count1 = jdbcTemplate.queryForObject(existTableSql, new Object[]{}, Integer.class);
        if (count1 < 1) {
            String createTableSql = getCreateSampleTableSql(sampleTableName);
            jdbcTemplate.execute(createTableSql);
        }

        String existDataTableSql = "SELECT COUNT(*) FROM information_schema.\n" +
                "TABLES WHERE TABLE_NAME='" + dataTableName + "'";

        Integer count2 = jdbcTemplate.queryForObject(existDataTableSql, new Object[]{}, Integer.class);
        if (count2 < 1) {
            String createTableSql = getCreateWaveDataTableSql(dataTableName);
            jdbcTemplate.execute(createTableSql);
        }
    }

    private Boolean checkTableExists(JdbcTemplate jdbcTemplate, String tableName) {
        String existTableSql = "SELECT COUNT(*) FROM information_schema.\n" +
                "TABLES WHERE TABLE_NAME='" + tableName + "'";

        Integer count = jdbcTemplate.queryForObject(existTableSql, new Object[]{}, Integer.class);
        return count > 0;
    }

    private String getCreateSampleTableSql(String tableName) {
        return "CREATE TABLE `" + tableName + "` (\n" +
                "  `id` char(32) NOT NULL,\n" +
                "  `deviceCode` char(32) DEFAULT NULL,\n" +
                "  `pointCode` char(32) DEFAULT NULL,\n" +
                "  `sampleTime` datetime DEFAULT NULL,\n" +
                "  `sampleFrequence` float DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    }

    private String getCreateWaveDataTableSql(String tableName) {
        return "CREATE TABLE `" + tableName + "` (\n" +
                "  `id` char(32) NOT NULL,\n" +
                "  `data` blob DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    }

    private String getSampleTablename(int year) {
        String tableName = "wave_sample_data_" + year;
        return tableName;
    }

    private String getWavaDataTablename(int year) {
        String tableName = "wave_data_" + year;
        return tableName;
    }
}
